﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using University.Workshop.Model;
using System.Data.SqlClient;

namespace University.Workshop.DataAccess
{
    public static class ConsultationDataAccess// : IDisposable
    {
        static MedicalCenterEntities entities = MedicalCenterEntities.Instance;

        public const string ConnString = "Data Source=.\\SQLEXPRESS;Initial Catalog=MedicalDatabase;Integrated Security = True";

        public static Consultation InsertConsultation(Consultation consultation)
        {
            if (consultation != null)
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                SqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "INSERT INTO [Consultation](PatientId,DoctorId,AppointmentId,FamilyHistory,FamilyHistoryDetails,PastMedicalHistory," +
                "PastMedicalHistoryDetails,PersonalHistory,PersonalHistoryDetails,HeartRate,BloodPressure," +
                "RespirationRate,Temperature,CurrentWeight,Height,IdealWeight,ChiefComplaints,GeneralSymptoms," +

                "CurrentCondition,CurrentConditionDetails,PreviousDiagnoses,PreviouslyEmployedTherapeutic,DigestiveApparatus,CardiovascularApparatus,RespiratoryApparatus,UrinaryApparatus," +
                "GenitalApparatus,HematologicApparatus,EndocrineSystem,MusculoskeletalSystem,NervousSystem,SensorialSystem,Psychosomatic," +
                "GeneralExamination,ExaminationHead,ExaminationNeck,ExaminationChest,ExaminationAbdomen,ExaminationMembers,ExaminationGenitals," +

                "Comments,Diagnosis,Prognosis,Treatment) VALUES(@PatientId,@DoctorId,@AppointmentId,@FamilyHistory,@FamilyHistoryDetails," + 
                "@PastMedicalHistory,@PastMedicalHistoryDetails,@PersonalHistory,@PersonalHistoryDetails," +
                "@HeartRate,@BloodPressure,@RespirationRate,@Temperature,@CurrentWeight,@Height," +
                "@IdealWeight,@ChiefComplaints,@GeneralSymptoms,@CurrentCondition,@CurrentConditionDetails,@PreviousDiagnoses," +

                "@PreviouslyEmployedTherapeutic,@DigestiveApparatus,@CardiovascularApparatus,@RespiratoryApparatus,@UrinaryApparatus," +
                "@GenitalApparatus,@HematologicApparatus,@EndocrineSystem,@OsteomuscularSystem,@NervousSystem," +
                "@SensorialSystem,@Psicosomatic,@GeneralExamination,@ExaminationHead,@ExaminationNeck," +
                "@ExaminationChest,@ExaminationAbdomen,@ExaminationMembers,@ExaminationGenitals," + 


                "@Comments,@Diagnosis,@Prognosis,@Treatment)";

                cmd.Parameters.AddWithValue("PatientId", consultation.PatientId);
                cmd.Parameters.AddWithValue("DoctorId", consultation.DoctorId);
                cmd.Parameters.AddWithValue("AppointmentId", consultation.AppointmentId);

                cmd.Parameters.AddWithValue("FamilyHistory", consultation.FamilyHistory);
                cmd.Parameters.AddWithValue("FamilyHistoryDetails", consultation.FamilyHistoryDetails);
                cmd.Parameters.AddWithValue("PastMedicalHistory", consultation.PastMedicalHistory);
                cmd.Parameters.AddWithValue("PastMedicalHistoryDetails", consultation.PastMedicalHistoryDetails);
                cmd.Parameters.AddWithValue("PersonalHistory", consultation.PersonalHistory);
                cmd.Parameters.AddWithValue("PersonalHistoryDetails", consultation.PersonalHistoryDetails);

                cmd.Parameters.AddWithValue("HeartRate", consultation.HeartRate);
                cmd.Parameters.AddWithValue("BloodPressure", consultation.BloodPressure);
                cmd.Parameters.AddWithValue("RespirationRate", consultation.RespirationRate);
                cmd.Parameters.AddWithValue("Temperature", consultation.Temperature);
                cmd.Parameters.AddWithValue("CurrentWeight", consultation.CurrentWeight);
                cmd.Parameters.AddWithValue("Height", consultation.Height);
                cmd.Parameters.AddWithValue("IdealWeight", consultation.IdealWeight);

                cmd.Parameters.AddWithValue("ChiefComplaints", consultation.ChiefComplaints);
                cmd.Parameters.AddWithValue("GeneralSymptoms", consultation.GeneralSymptoms);

                cmd.Parameters.AddWithValue("CurrentCondition", consultation.CurrentCondition);
                cmd.Parameters.AddWithValue("CurrentConditionDetails", consultation.CurrentConditionDetails);
                cmd.Parameters.AddWithValue("PreviousDiagnoses", consultation.PreviousDiagnoses);
                cmd.Parameters.AddWithValue("PreviouslyEmployedTherapeutic", consultation.PreviouslyEmployedTherapeutic);
                
                cmd.Parameters.AddWithValue("DigestiveApparatus", consultation.DigestiveApparatus);
                cmd.Parameters.AddWithValue("CardiovascularApparatus", consultation.CardiovascularApparatus);
                cmd.Parameters.AddWithValue("RespiratoryApparatus", consultation.RespiratoryApparatus);
                cmd.Parameters.AddWithValue("UrinaryApparatus", consultation.UrinaryApparatus);
                cmd.Parameters.AddWithValue("GenitalApparatus", consultation.GenitalApparatus);
                cmd.Parameters.AddWithValue("HematologicApparatus", consultation.HematologicApparatus);
                cmd.Parameters.AddWithValue("EndocrineSystem", consultation.EndocrineSystem);
                cmd.Parameters.AddWithValue("OsteomuscularSystem", consultation.MusculoskeletalSystem);
                cmd.Parameters.AddWithValue("NervousSystem", consultation.NervousSystem);
                cmd.Parameters.AddWithValue("SensorialSystem", consultation.SensorialSystem);
                cmd.Parameters.AddWithValue("Psicosomatic", consultation.Psychosomatic);

                cmd.Parameters.AddWithValue("GeneralExamination", consultation.GeneralExamination);
                cmd.Parameters.AddWithValue("ExaminationHead", consultation.ExaminationHead);
                cmd.Parameters.AddWithValue("ExaminationNeck", consultation.ExaminationNeck);
                cmd.Parameters.AddWithValue("ExaminationChest", consultation.ExaminationChest);
                cmd.Parameters.AddWithValue("ExaminationAbdomen", consultation.ExaminationAbdomen);
                cmd.Parameters.AddWithValue("ExaminationMembers", consultation.ExaminationMembers);
                cmd.Parameters.AddWithValue("ExaminationGenitals", consultation.ExaminationGenitals);
                
                //cmd.Parameters.AddWithValue("AppointmentId", consultation.AppointmentId);
                ///cmd.Parameters.AddWithValue("ConsultationDate", consultation.ConsultationDate);
                //cmd.Parameters.AddWithValue("ConsultationStatus", consultation.ConsultationStatus);

                cmd.Parameters.AddWithValue("Comments", consultation.Comments);
                cmd.Parameters.AddWithValue("Diagnosis", consultation.Diagnosis);
                cmd.Parameters.AddWithValue("Prognosis", consultation.Prognosis);
                cmd.Parameters.AddWithValue("Treatment", consultation.Treatment);

                SqlCommand cmdId = conn.CreateCommand();
                cmdId.CommandText = "SELECT @@Identity";
                try
                {
                    conn.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error inserting a consultation in the database");
                    }
                    //consultation.ConsultationId = (long)cmdId.ExecuteScalar();
                    return consultation;
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error inserting a consultation in the database, SQL Exception" + ex);
                    
                }
                /*finally
                {
                    if (conn != null && conn.State == System.Data.ConnectionState.Open)
                    {
                        //conn.Close();
                        
                    }
                    conn.Dispose();
                    
                }*/
                }

            }
            else
                return null;
        }


        public static void UpdateConsultation(string consultationId)
        {

                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "UPDATE [Consultation] SET [ConsultationStatus] = 0 WHERE [ConsultationId] = @ConsultationId";
                    cmd.Parameters.AddWithValue("ConsultationId", consultationId);


                    
                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error inserting a payment in the database");
                        }
                        //return payment;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error inserting a payment in the database SQL Exception" + ex);
                    }
                }
            
        }




        public static ICollection<Consultation> SearchConsultation(string patientId)
        {
            if(!string.IsNullOrEmpty(patientId))
            {
                var consultations = from consultation in entities.Consultation
                                    where consultation.PatientId == patientId
                                    where consultation.ConsultationStatus == "1"
                                    select consultation;
                return consultations.ToList();
            }
            return null;
        }

        public static Consultation ShowConsultation(long idConsultation)
        {
            return entities.Consultation.Find(idConsultation);

        }

        public static Consultation ShowConsultationAppointment(long idConsultation)
        {
            var consultations = from consultation in entities.Consultation
                                    where consultation.AppointmentId == idConsultation
                                    select consultation;
                return consultations.First();
            //}
            //return null;

        }


        /*
        public void Dispose()
        {
            /*Dispose(true);
            GC.SuppressFinalize(this);*/
        /*}

        private void Dispose(bool disposing)
        {
            /*if (disposing)
            {
                myConnection.Close();
                myDataAdapter.Dispose();
                mySelectCommand.Dispose();
                myDataSet.Dispose();
                if (instance != null)
                {
                    instance = null;
                }
            }
        }*/
    }
}
